// src/main/java/com/fengchao/dao/GeneralDao.java
package com.cdvtc.fengchao.dao;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class GeneralDao {
    private static final String URL = "jdbc:mysql://localhost:3306/fengchao?useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC";
    private static final String USER = "root";
    private static final String PASSWORD = "111111";

    // 获取数据库连接
    private Connection getConnection() throws SQLException {
        return DriverManager.getConnection(URL, USER, PASSWORD);
    }

    // 通用查询方法
    public List<Map<String, Object>> query(String sql, Object... params) {
        List<Map<String, Object>> result = new ArrayList<>();
        try (Connection conn = getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            // 设置参数
            for (int i = 0; i < params.length; i++) {
                pstmt.setObject(i + 1, params[i]);
            }

            // 执行查询
            try (ResultSet rs = pstmt.executeQuery()) {
                ResultSetMetaData metaData = rs.getMetaData();
                int columnCount = metaData.getColumnCount();

                // 处理结果集
                while (rs.next()) {
                    Map<String, Object> row = new HashMap<>();
                    for (int i = 1; i <= columnCount; i++) {
                        row.put(metaData.getColumnName(i), rs.getObject(i));
                    }
                    result.add(row);
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return result;
    }

    // 通用更新方法（增删改）
    public int update(String sql, Object... params) {
        int rows = 0;
        try (Connection conn = getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            // 设置参数
            for (int i = 0; i < params.length; i++) {
                pstmt.setObject(i + 1, params[i]);
            }

            // 执行更新
            rows = pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return rows;
    }

    // 获取自增主键
    public int getLastInsertId() {
        List<Map<String, Object>> result = query("SELECT LAST_INSERT_ID() as id");
        if (!result.isEmpty()) {
            return Integer.parseInt(result.get(0).get("id").toString());
        }
        return -1;
    }

    // -------------- Users 表操作 --------------

    // 添加用户
    public int addUser(String username, String phone, String address, String email, String password) {
        String sql = "INSERT INTO users (username, phone, address, email, password) VALUES (?, ?, ?, ?, ?)";
        return update(sql, username, phone, address, email, password);
    }

    // 删除用户
    public int deleteUser(int id) {
        String sql = "DELETE FROM users WHERE id = ?";
        return update(sql, id);
    }

    // 更新用户
    public int updateUser(int id, String username, String phone, String address, String email, String password) {
        String sql = "UPDATE users SET username = ?, phone = ?, address = ?, email = ?, password = ? WHERE id = ?";
        return update(sql, username, phone, address, email, password, id);
    }

    // 查询用户
    public List<Map<String, Object>> getUsers() {
        String sql = "SELECT * FROM users";
        return query(sql);
    }

    // 根据ID查询用户
    public Map<String, Object> getUserById(int id) {
        String sql = "SELECT * FROM users WHERE id = ?";
        List<Map<String, Object>> result = query(sql, id);
        return result.isEmpty() ? null : result.get(0);
    }

    // -------------- Products 表操作 --------------

    // 添加产品
    public int addProduct(String photo, String name, String category, String price, String features, int supplierId, int stock) {
        String sql = "INSERT INTO products (photo, name, category, price, features, supplier_id, stock) VALUES (?, ?, ?, ?, ?, ?, ?)";
        return update(sql, photo, name, category, price, features, supplierId, stock);
    }

    // 删除产品
    public int deleteProduct(int id) {
        String sql = "DELETE FROM products WHERE id = ?";
        return update(sql, id);
    }

    // 更新产品
    public int updateProduct(int id, String photo, String name, String category, String price, String features, int supplierId, int stock) {
        String sql = "UPDATE products SET photo = ?, name = ?, category = ?, price = ?, features = ?, supplier_id = ?, stock = ? WHERE id = ?";
        return update(sql, photo, name, category, price, features, supplierId, stock, id);
    }

    // 查询产品
    public List<Map<String, Object>> getProducts() {
        String sql = "SELECT * FROM products";
        return query(sql);
    }

    // 根据ID查询产品
    public Map<String, Object> getProductById(int id) {
        String sql = "SELECT * FROM products WHERE id = ?";
        List<Map<String, Object>> result = query(sql, id);
        return result.isEmpty() ? null : result.get(0);
    }

    // -------------- Orders 表操作 --------------

    // 添加订单
    public int addOrder(int userId, String purchaseTime, String paymentStatus, String paymentMethod, double totalAmount) {
        String sql = "INSERT INTO orders (user_id, purchase_time, payment_status, payment_method, total_amount) VALUES (?, ?, ?, ?, ?)";
        return update(sql, userId, purchaseTime, paymentStatus, paymentMethod, totalAmount);
    }

    // 删除订单
    public int deleteOrder(int id) {
        String sql = "DELETE FROM orders WHERE id = ?";
        return update(sql, id);
    }

    // 更新订单
    public int updateOrder(int id, int userId, String purchaseTime, String paymentStatus, String paymentMethod, double totalAmount) {
        String sql = "UPDATE orders SET user_id = ?, purchase_time = ?, payment_status = ?, payment_method = ?, total_amount = ? WHERE id = ?";
        return update(sql, userId, purchaseTime, paymentStatus, paymentMethod, totalAmount, id);
    }

    // 查询订单
    public List<Map<String, Object>> getOrders() {
        String sql = "SELECT * FROM orders";
        return query(sql);
    }

    // 根据ID查询订单
    public Map<String, Object> getOrderById(int id) {
        String sql = "SELECT * FROM orders WHERE id = ?";
        List<Map<String, Object>> result = query(sql, id);
        return result.isEmpty() ? null : result.get(0);
    }

    // -------------- OrderDetails 表操作 --------------

    // 添加订单详情
    public int addOrderDetail(int orderId, int productId, int quantity, double totalPrice) {
        String sql = "INSERT INTO order_details (order_id, product_id, quantity, total_price) VALUES (?, ?, ?, ?)";
        return update(sql, orderId, productId, quantity, totalPrice);
    }

    // 删除订单详情
    public int deleteOrderDetail(int id) {
        String sql = "DELETE FROM order_details WHERE id = ?";
        return update(sql, id);
    }

    // 更新订单详情
    public int updateOrderDetail(int id, int orderId, int productId, int quantity, double totalPrice) {
        String sql = "UPDATE order_details SET order_id = ?, product_id = ?, quantity = ?, total_price = ? WHERE id = ?";
        return update(sql, orderId, productId, quantity, totalPrice, id);
    }

    // 查询订单详情
    public List<Map<String, Object>> getOrderDetails() {
        String sql = "SELECT * FROM order_details";
        return query(sql);
    }

    // 根据ID查询订单详情
    public Map<String, Object> getOrderDetailById(int id) {
        String sql = "SELECT * FROM order_details WHERE id = ?";
        List<Map<String, Object>> result = query(sql, id);
        return result.isEmpty() ? null : result.get(0);
    }

    // -------------- Suppliers 表操作 --------------

    // 添加供应商
    public int addSupplier(String phone, String address) {
        String sql = "INSERT INTO suppliers (phone, address) VALUES (?, ?)";
        return update(sql, phone, address);
    }

    // 删除供应商
    public int deleteSupplier(int id) {
        String sql = "DELETE FROM suppliers WHERE id = ?";
        return update(sql, id);
    }

    // 更新供应商
    public int updateSupplier(int id, String phone, String address) {
        String sql = "UPDATE suppliers SET phone = ?, address = ? WHERE id = ?";
        return update(sql, phone, address, id);
    }

    // 查询供应商
    public List<Map<String, Object>> getSuppliers() {
        String sql = "SELECT * FROM suppliers";
        return query(sql);
    }

    // 根据ID查询供应商
    public Map<String, Object> getSupplierById(int id) {
        String sql = "SELECT * FROM suppliers WHERE id = ?";
        List<Map<String, Object>> result = query(sql, id);
        return result.isEmpty() ? null : result.get(0);
    }

    // -------------- UserAddresses 表操作 --------------

    // 添加用户地址
    public int addUserAddress(int userId, String address, int isDefault) {
        String sql = "INSERT INTO user_addresses (user_id, address, is_default) VALUES (?, ?, ?)";
        return update(sql, userId, address, isDefault);
    }

    // 删除用户地址
    public int deleteUserAddress(int id) {
        String sql = "DELETE FROM user_addresses WHERE id = ?";
        return update(sql, id);
    }

    // 更新用户地址
    public int updateUserAddress(int id, int userId, String address, int isDefault) {
        String sql = "UPDATE user_addresses SET user_id = ?, address = ?, is_default = ? WHERE id = ?";
        return update(sql, userId, address, isDefault, id);
    }

    // 查询用户地址
    public List<Map<String, Object>> getUserAddresses() {
        String sql = "SELECT * FROM user_addresses";
        return query(sql);
    }

    // 根据ID查询用户地址
    public Map<String, Object> getUserAddressById(int id) {
        String sql = "SELECT * FROM user_addresses WHERE id = ?";
        List<Map<String, Object>> result = query(sql, id);
        return result.isEmpty() ? null : result.get(0);
    }

    // 根据用户ID查询地址
    public List<Map<String, Object>> getUserAddressesByUserId(int userId) {
        String sql = "SELECT * FROM user_addresses WHERE user_id = ?";
        return query(sql, userId);
    }
}